package package02;

import java.sql.*;

/**
 * @Description TODO
 * @Author zzj
 * @Date 2020/7/16 16:51
 */
public class FlowDaoImp implements FlowDao {
    private DBUtil dbUtil = new DBUtil();
    private PreparedStatement pst;
    private ResultSet rs;


    // 插入数据
    /**
    * @Description 将flow传入save方法，进行数据的插入
    * @param flow
    * @param index
    * @Return void
    * @Author zzj
    * @Date 2020/7/18 14:52
    **/
    @Override
    public void save(Flow flow,int index) {
        // 获取会话
        Connection connection = dbUtil.init();
        // 声明sql语句
        String sql = "insert into flow values(?,?,?,?,?,?,?,?)";
        try {
            // 预编译sql
            pst = connection.prepareStatement(sql);
            // 设定参数
            pst.setInt(1, index);
            pst.setTimestamp(2, new Timestamp(flow.getAccessTime().getTime()));
            pst.setString(3, flow.getPhone());
            pst.setString(4, flow.getSrc());
            pst.setString(5, flow.getUrl());
            pst.setInt(6, flow.getUpStream());
            pst.setInt(7, flow.getDownStream());
            pst.setString(8, flow.getStatus());
            // 执行更新表（插入）
            int count = pst.executeUpdate(); // 受影响的行数
            System.out.println(count);// 1
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbUtil.close();
        }
    }

    /**
    * @Description 计算上行总流量
    * @param
    * @Return package02.Flow
    * @Author zzj
    * @Date 2020/7/18 14:36
    **/
    @Override
    public Flow countUpStream()  {
        // 获取会话
        Connection connection = dbUtil.init();
        // 声明sql语句
        String sql = "select sum(UPSTREAM) from flow";
        try {
            // 预编译
            Statement statement = connection.createStatement();
            // 执行sql，返回结果集
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()){
                System.out.println("上行总流量："+rs.getInt(1));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    /**
    * @Description 计算下行总流量
    * @param
    * @Return package02.Flow
    * @Author zzj
    * @Date 2020/7/18 14:38
    **/
    @Override
    public Flow countDownStream() {
        // 获取会话
        Connection connection = dbUtil.init();
        // 声明sql语句
        String sql = "select sum(DOWNSTREAM) from flow";
        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()){
                System.out.println("下行总流量："+rs.getInt(1));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    /**
    * @Description 按手机号进行分组计算每个用户的总流量
    * @param
    * @Return package02.Flow
    * @Author zzj
    * @Date 2020/7/18 14:39
    **/
    @Override
    public Flow countStream() {
        // 获取会话
        Connection connection = dbUtil.init();
        // 声明sql语句
        String sql = "select phone,nvl(UPSTREAM,0)+nvl(DOWNSTREAM,0) from flow";
        try {
            // 预编译
            Statement statement = connection.createStatement();
            // 执行sql，返回结果集
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()){
                System.out.println("手机号为："+rs.getString(1)+" 的总流量为："+rs.getInt(2));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }


}

